package com.elite.groovy.dev;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;
import java.util.Map;

public class DevTest {
    private static final Log log = LogFactory.getLog(DevTest.class);
    private static JdbcTemplate jt = TxConfigUAT.getJdbcTemplate();
    private static long startTime = System.currentTimeMillis();
    private static long costTime = System.currentTimeMillis();

    public static void main(String[] args) throws Exception {

        long total_Cost = 0;
        String sel_SQL1 = "select '呼入电话' ,\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 ' \n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=1  and ec.CREATETIME>? \n" + "and ec.CREATETIME<?  and e.handlegroup_id='ORG016'\n" + "union \n" + "select '呼出电话',\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 '\n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=2  and ec.CREATETIME>?  \n" + "and ec.CREATETIME<?  \n" + "and e.handlegroup_id='ORG016'  ";
        String sel_SQL2 = "select '呼入电话' ,\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 ' \n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=1  and ec.CREATETIME>to_date(?,'YYYY-MM-DD hh24:mi:ss')  \n" + "and ec.CREATETIME<to_date(?,'YYYY-MM-DD hh24:mi:ss')   and e.handlegroup_id='ORG016'\n" + "union \n" + "select '呼出电话',\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 '\n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=2  and ec.CREATETIME>to_date(?,'YYYY-MM-DD hh24:mi:ss')   \n" + "and ec.CREATETIME<to_date(?,'YYYY-MM-DD hh24:mi:ss')   \n" + "and e.handlegroup_id='ORG016'  ";
        String sel_SQL3 = "select '呼入电话' ,\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 ' \n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=1  and ec.CREATETIME>'2024-12-01 00:00:00'  \n" + "and ec.CREATETIME<'2024-12-12 00:00:00'   and e.handlegroup_id='ORG016'\n" + "union \n" + "select '呼出电话',\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 '\n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=2  and ec.CREATETIME>'2024-12-01 00:00:00'   \n" + "and ec.CREATETIME<'2024-12-12 00:00:00'   \n" + "and e.handlegroup_id='ORG016'  ";
        String sel_SQL4 = "select '呼入电话' ,\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 ' \n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=1  and ec.CREATETIME>to_date('2024-12-01 00:00:00','YYYY-MM-DD hh24:mi:ss')  \n" + "and ec.CREATETIME<to_date('2024-12-12 00:00:00','YYYY-MM-DD hh24:mi:ss')   and e.handlegroup_id='ORG016'\n" + "union \n" + "select '呼出电话',\n" + "    count(e.event_guid),\n" + "    nvl(sum(case when callestablished <> 1 then 1 else 0 end),0),\n" + "    nvl(floor((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(e.workendtime-e.workstarttime)*24*60*60))),60),0)|| '秒 ',\n" + "    nvl(floor((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60)))/3600),0)|| '小时 '||\n" + "    nvl(floor(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),3600)/60),0)|| '分 '||\n" + "    nvl(mod((sum(round(to_number(case when callestablished = '1' then (e.callendtime-e.callstarttime) else 0 end)*24*60*60))),60),0)|| '秒 '\n" + "from tabccalsum tc \n" + "left join event e  on tc.relation_id=e.EVENT_guid\n" + "left join EVENTCALLNATURE ec on ec.event_guid=tc.relation_id\n" + "where e.calltype=2  and ec.CREATETIME>to_date('2024-12-01 00:00:00','YYYY-MM-DD hh24:mi:ss')   \n" + "and ec.CREATETIME<to_date('2024-12-12 00:00:00','YYYY-MM-DD hh24:mi:ss')   \n" + "and e.handlegroup_id='ORG016'  ";

        int loop_ = 1;
        int loop_Limit = 10;
        while (loop_ <= loop_Limit) {
            startTime = System.currentTimeMillis();
            List<Map<String, Object>> res_List1 = jt.queryForList(sel_SQL1, "2024-12-01 00:00:00", "2024-12-12 00:00:00", "2024-12-01 00:00:00", "2024-12-12 00:00:00");
            costTime = System.currentTimeMillis() - startTime;
            log.debug(loop_ + "➡查询结果:" + res_List1.size() + " 耗时:" + costTime +" ?");

            startTime = System.currentTimeMillis();
            List<Map<String, Object>> res_List2 = jt.queryForList(sel_SQL2, "2024-12-01 00:00:00", "2024-12-12 00:00:00", "2024-12-01 00:00:00", "2024-12-12 00:00:00");
            costTime = System.currentTimeMillis() - startTime;
            log.debug(loop_ + "☣查询结果:" + res_List2.size() + " 耗时:" + costTime+" ?To_Date");

            startTime = System.currentTimeMillis();
            List<Map<String, Object>> res_List3 = jt.queryForList(sel_SQL3);
            costTime = System.currentTimeMillis() - startTime;
            log.debug(loop_ + "♐查询结果:" + res_List3.size() + " 耗时:" + costTime+" 字符串");

            startTime = System.currentTimeMillis();
            List<Map<String, Object>> res_List4 = jt.queryForList(sel_SQL4);
            costTime = System.currentTimeMillis() - startTime;
            log.debug(loop_ + "♦查询结果:" + res_List4.size() + " 耗时:" + costTime+" 字符串To_Date");

            total_Cost = total_Cost + costTime;

            loop_++;
        }

        log.debug("ℹ查询次数:" + loop_Limit + " ⌛平均耗时:" + total_Cost / loop_Limit);

    }

}
